package com.itvita.code.generator.service;

import com.itvita.code.generator.entity.ColumnInfo;
import com.itvita.code.generator.entity.TableInfo;
import com.itvita.code.generator.mapper.ColumnInfoMapper;
import com.itvita.code.generator.mapper.TableInfoMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.SQLException;
import java.util.List;

@Slf4j
public class GeneratorService {

    private JdbcTemplate jdbcTemplate;

    public GeneratorService(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * @Time: 2021/1/22 10:41 上午
     * @author: liu.q [916000612@qq.com]
     * @des: 查询数据库表信息
     */
    public List<TableInfo> getTableInfo(String schema) {
        String sql = "select table_name,table_comment from information_schema.`tables` where table_schema = '"
                + schema + "' order by table_name desc";
        log.debug(sql);
        List<TableInfo> list = jdbcTemplate.query(sql, new TableInfoMapper());
        try {
            jdbcTemplate.getDataSource().getConnection().close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    /**
     * @Time: 2021/1/22 10:41 上午
     * @author: liu.q [916000612@qq.com]
     * @des: 查询表信息
     */
    public TableInfo getOneTable(String schema, String tableName) {
        String sql = "select table_name,table_comment from information_schema.`tables` where table_schema = '"
                + schema + "' and table_name='" + tableName + "' order by table_name desc";
        log.debug(sql);
        TableInfo tableInfo = jdbcTemplate.queryForObject(sql, new TableInfoMapper());
        try {
            jdbcTemplate.getDataSource().getConnection().close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return tableInfo;
    }

    /**
     * @Time: 2021/1/22 10:42 上午
     * @author: liu.q [916000612@qq.com]
     * @des: 查询数据库表 - 列信息
     */
    public List<ColumnInfo> getColumnInfo(String tableName) {
        String sql = "    SELECT\n" +
                "   t.COLUMN_NAME columnName,\n" +
                "   t.COLUMN_COMMENT columnComment,\n" +
                "   t.DATA_TYPE dataType\n" +
                " FROM information_schema.`COLUMNS` t\n" +
                " WHERE t.TABLE_SCHEMA = (select database()) AND t.TABLE_NAME = '" + tableName + "'";

        log.debug(sql);
        List<ColumnInfo> list = jdbcTemplate.query(sql, new ColumnInfoMapper());
        try {
            jdbcTemplate.getDataSource().getConnection().close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }
}
